const xlsx = require('node-xlsx')
const config = require('./config')
const xlsFileData = xlsx.parse(config.inputDataPath)
const request = require('superagent')
const { log4js } = require('./tool/logs')
const { queryAreaCode, addAreaCode } = require('./modal/index')
const { queryTownship, addTownship } = require('./modal/township')
//logger 是log4js的实例
const logger = log4js.getLogger()

/**
 * @description 通过地址查询经纬度
 * @param {string} address 查询的地址
*/
function queryGPSByAddress(address) {
  return new Promise((resolve, reject) => {
    request.get('https://api.map.baidu.com/geocoding/v3/')
    .query({ address: address })
    .query({ output: 'json' })
    .query({ak: config.baiduApiAk})
    .end(function(err, res) {
      if (err) {
        console.log(err)
        reject(err)
      }
      const response = JSON.parse(res.text)
      console.log(response)
      resolve(response)
    })
  })
}

// 解析行数据
async function parseRowDate(rowData) {
  return new Promise(async (resolve, reject) => {
    // 第一列为省份
    // 第二列为行政区
    // 第三列为查询到的经纬度
    let cell1 = rowData[0]
    , cell2 = rowData[1]
  
    , cell4 = rowData[3]
    , cell5 = rowData[4]
  
    , cell7 = rowData[6]
    , cell8 = rowData[7]
  
    , cell10 = rowData[9]
    , cell11 = rowData[10]
    
    // 第一，第二列大概率是省份
    if (cell1 && cell2) {
      const queryArea = await queryAreaCode(cell2)
      if (queryArea && queryArea.length === 0) { // 编码没有入库
        const parseAddress = await queryGPSByAddress(cell2)
        if (parseAddress.status === 0) {
          const result = parseAddress.result
          logger.info(JSON.stringify(result))
          const insertVal = {
            area_code: cell2,
            area_name: cell1,
            lng: result.location.lng,
            lat: result.location.lat,
            comprehension: result.comprehension,
            level: result.level,
          }
          let addRes = await addAreaCode(insertVal)
          console.log('addRes', addRes)
          logger.info(`编码 ${cell2}, 名称 ${cell1} 入库`)
        } else {
          logger.error(`编码 ${cell2}, 名称 ${cell1} 失败`)
          logger.error(parseAddress)
          reject(parseAddress)
        }
      } else { // 已经有入库编码
        logger.info(`编码 ${cell2}, 名称 ${cell1} 已入库`)
      }
    } else {
      logger.error(`省份数据有误: ${cell1} - ${cell2}`)
      reject()
    }
    // 第四，第五，大概率是城市
    if (cell4 && cell5) {
      const queryArea = await queryAreaCode(cell5)
      if (queryArea && queryArea.length === 0) { // 编码没有入库
        const parseAddress = await queryGPSByAddress(cell1 + cell4)
        if (parseAddress.status === 0) {
          const result = parseAddress.result
          logger.info(JSON.stringify(result))
          const insertVal = {
            area_code: cell5,
            area_name: cell4,
            lng: result.location.lng,
            lat: result.location.lat,
            comprehension: result.comprehension,
            level: result.level,
          }
          let addRes = await addAreaCode(insertVal)
          console.log('addRes', addRes)
          logger.info(`编码 ${cell5}, 名称 ${cell4} 入库`)
        } else {
          logger.error(`编码 ${cell5}, 名称 ${cell4} 失败`)
          logger.error(parseAddress)
          reject(parseAddress)
        }
      } else { // 已经有入库编码
        logger.info(`编码 ${cell5}, 名称 ${cell4} 已入库`)
      }
    } else {
      logger.error(`城市数据有误: ${cell4} - ${cell5}`)
      reject()
    }
    // 第七，第八，大概率是区
    if (cell7 && cell8) {
      const queryArea = await queryAreaCode(cell8)
      if (queryArea && queryArea.length === 0) { // 编码没有入库
        const parseAddress = await queryGPSByAddress(cell1 + cell4 + cell7)
        if (parseAddress.status === 0) {
          const result = parseAddress.result
          logger.info(JSON.stringify(result))
          const insertVal = {
            area_code: cell8,
            area_name: cell7,
            lng: result.location.lng,
            lat: result.location.lat,
            comprehension: result.comprehension,
            level: result.level,
          }
          let addRes = await addAreaCode(insertVal)
          console.log('addRes', addRes)
          logger.info(`编码 ${cell8}, 名称 ${cell7} 入库`)
        } else {
          logger.error(`编码 ${cell8}, 名称 ${cell7} 失败`)
          logger.error(parseAddress)
          reject(parseAddress)
        }
      } else { // 已经有入库编码
        logger.info(`编码 ${cell8}, 名称 ${cell7} 已入库`)
      }
    } else {
      logger.error(`区域数据有误: ${cell7} - ${cell8}`)
      reject()
    }
    // 第10，第11，大概率是街道
    if (cell10 && cell11) {
      const queryArea = await queryAreaCode(cell11)
      if (queryArea && queryArea.length === 0) { // 编码没有入库
        const parseAddress = await queryGPSByAddress(cell1 + cell4 + cell7 + cell10)
        if (parseAddress.status === 0) {
          const result = parseAddress.result
          logger.info(JSON.stringify(result))
          const insertVal = {
            area_code: cell11,
            area_name: cell10,
            lng: result.location.lng,
            lat: result.location.lat,
            comprehension: result.comprehension,
            level: result.level,
          }
          let addRes = await addAreaCode(insertVal)
          console.log('addRes', addRes)
          logger.info(`编码 ${cell11}, 名称 ${cell10} 入库`)
        } else {
          logger.error(`编码 ${cell11}, 名称 ${cell10} 失败`)
          logger.error(parseAddress)
          reject(parseAddress)
        }
      } else { // 已经有入库编码
        logger.info(`编码 ${cell11}, 名称 ${cell10} 已入库`)
      }
    } else {
      logger.error(`街道数据有误: ${cell10} - ${cell11}`)
      reject()
    }
    resolve()
  })
}

// 处理行数据
function parseRow2Db(rowData) {
  return new Promise(async (resolve, reject) => {
    let cell1 = rowData[0]
    , cell2 = rowData[1]
    , cell_1_2_id = null

    , cell4 = rowData[3]
    , cell5 = rowData[4]
    , cell_4_5_id = null

    , cell7 = rowData[6]
    , cell8 = rowData[7]
    , cell_7_8_id = null

    , cell10 = rowData[9]
    , cell11 = rowData[10]
    , cell_10_11_id = null
    // 处理1， 2 列
    if (cell1 && cell2) {
      // 从 address_gps 表查找数据
      let queryArea = await queryAreaCode(cell2)
      let township = await queryTownship(cell2)
      if (township && township.length === 0) {
        if (queryArea && queryArea.length) {
          queryArea = queryArea[0]
          const insertVal = {
            code: cell2,
            name: cell1,
            lat: queryArea.lat,
            lng: queryArea.lng,
            parent_id: 1,
            level: 1,
            parent_code: 100000
          }
          const inssetRes = await addTownship(insertVal)
          cell_1_2_id = inssetRes.insertId
          logger.info(`编码 ${cell2}, 名称 ${cell1} 入库, id ${cell_1_2_id}`)
        } else {
          logger.error(`未查找到数据: ${cell2} - ${cell1}`)
        }
      } else {
        cell_1_2_id = township[0].id
        logger.info(`编码 ${cell2}, 名称 ${cell1}, id ${cell_1_2_id} 已入库`)
      }
    } else {
      logger.error(`数据有误: ${cell2} - ${cell1}`)
      reject()
    }
    // 处理4， 5 列
    if (cell4 && cell5) {
      // 从 address_gps 表查找数据
      let queryArea = await queryAreaCode(cell5)
      let township = await queryTownship(cell5)
      if (township && township.length === 0) {
        if (queryArea && queryArea.length) {
          queryArea = queryArea[0]
          const insertVal = {
            code: cell5,
            name: cell4,
            lat: queryArea.lat,
            lng: queryArea.lng,
            parent_id: cell_1_2_id,
            level: 2,
            parent_code: cell2
          }
          const inssetRes = await addTownship(insertVal)
          cell_4_5_id = inssetRes.insertId
          logger.info(`编码 ${cell5}, 名称 ${cell4} 入库, id ${cell_4_5_id}`)
        } else {
          logger.error(`未查找到数据: ${cell5} - ${cell4}`)
        }
      } else {
        cell_4_5_id = township[0].id
        logger.info(`编码 ${cell5}, 名称 ${cell4}, id ${cell_4_5_id} 已入库`)
      }
    } else {
      logger.error(`数据有误: ${cell5} - ${cell4}`)
      reject()
    }
    // 处理7， 8 列
    if (cell7 && cell8) {
      // 从 address_gps 表查找数据
      let queryArea = await queryAreaCode(cell8)
      let township = await queryTownship(cell8)
      if (township && township.length === 0) {
        if (queryArea && queryArea.length) {
          queryArea = queryArea[0]
          const insertVal = {
            code: cell8,
            name: cell7,
            lat: queryArea.lat,
            lng: queryArea.lng,
            parent_id: cell_4_5_id,
            level: 3,
            parent_code: cell5
          }
          const inssetRes = await addTownship(insertVal)
          cell_7_8_id = inssetRes.insertId
          logger.info(`编码 ${cell8}, 名称 ${cell7} 入库, id ${cell_7_8_id}`)
        } else {
          logger.error(`未查找到数据: ${cell8} - ${cell7}`)
        }
      } else {
        cell_7_8_id = township[0].id
        logger.info(`编码 ${cell8}, 名称 ${cell7}, id ${cell_7_8_id} 已入库`)
      }
    } else {
      logger.error(`数据有误: ${cell8} - ${cell7}`)
      reject()
    }
    // 处理10， 11 列
    if (cell10 && cell11) {
      // 从 address_gps 表查找数据
      let queryArea = await queryAreaCode(cell11)
      let township = await queryTownship(cell11)
      if (township && township.length === 0) {
        if (queryArea && queryArea.length) {
          queryArea = queryArea[0]
          const insertVal = {
            code: cell11,
            name: cell10,
            lat: queryArea.lat,
            lng: queryArea.lng,
            parent_id: cell_7_8_id,
            level: 4,
            parent_code: cell8
          }
          const inssetRes = await addTownship(insertVal)
          cell_10_11_id = inssetRes.insertId
          logger.info(`编码 ${cell11}, 名称 ${cell10} 入库, id ${cell_10_11_id}`)
        } else {
          logger.error(`未查找到数据: ${cell11} - ${cell10}`)
        }
      } else {
        cell_10_11_id = township[0].id
        logger.info(`编码 ${cell11}, 名称 ${cell10}, id ${cell_10_11_id} 已入库`)
      }
    } else {
      logger.error(`数据有误: ${cell11} - ${cell7}`)
      reject()
    }
    resolve()
  })
}

// 解析 sheet 表格
function parseSheetList(sheetList) {
  sheetList.forEach(async (sheet, idx) => {
    // sheet 名称
    let sheetName = sheet.name
    // 表格的数据
    , sheetBody = sheet.data
    // 解析行数据
    if (sheetBody && sheetBody.length) {
      for (let i = 0; i < sheetBody.length; i++) {
        if (sheetBody[i] && sheetBody[i].length) {
          logger.info(i + '行数据' + JSON.stringify(sheetBody[i]))
          console.log(`处理第${i}行数据`)
          await parseRow2Db(sheetBody[i])
          // await parseRowDate(sheetBody[i])
        }
      }
      console.log("数据已处理完毕")
    }
  })
}

try {
  parseSheetList(xlsFileData)
  // queryGPSByAddress('云南省')
} catch(err) {
  console.log(err)
}